package DRC;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Date;

import DRCOMM.*;

public class DRCDB{

    public String ConfFile="drc.ini";
    public String DBFile="drc.db";
    public int WEBPort=1314;
    final protected int reg_length=256;

    private Connection sqlite_connn=null;

    public DRCDB(){
        try {
            this.read_conf();
        } catch (Exception e) {
            e.printStackTrace();
        }
        this.open_db();
    }

    public DRCDB(String ConfFile){
        this.ConfFile=ConfFile;
        try {
            this.read_conf();
        } catch (Exception e) {
            e.printStackTrace();
        }
        this.open_db();
    }

    public void open_db(){
        try {
            Class.forName("org.sqlite.JDBC");
            this.sqlite_connn = DriverManager.getConnection("jdbc:sqlite:"+this.DBFile, "drc", "123456");
            // this.sqlite_connn = DriverManager.getConnection("jdbc:sqlite:memory", "drc", "123456");
            // 自动提交设定为假后，必须手动提交
            // this.sqlite_connn.setAutoCommit(false);
            this.create_sqlite_db();
            System.out.println("DRC database successfully");
        } catch ( Exception e ) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            // System.exit(0);
        }
    }

    public void close_db(){
        try {
            this.sqlite_connn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public boolean executeUpdateSQL(String sqlstr){
        boolean result=true;
        try{
            Statement stmt = null;
            stmt = this.sqlite_connn.createStatement();
            stmt.execute(sqlstr);
            stmt.close();
            // this.sqlite_connn.commit();
        }catch(Exception e){
            result=false;
        }
        return result;
    }

    public record_data_set executeQuerySQL(String sqlstr){
        record_data_set result=new record_data_set();
        try{
            if(this.sqlite_connn.isClosed())
                this.sqlite_connn = DriverManager.getConnection("jdbc:sqlite:"+this.DBFile);
            Statement stmt = null;
            stmt = this.sqlite_connn.createStatement();
            ResultSet rs=stmt.executeQuery(sqlstr);
            List<String> columns=new ArrayList<String>();
            ResultSetMetaData rsmd=rs.getMetaData();
            for(int i=1;i<=rsmd.getColumnCount();i++){
                columns.add(rsmd.getColumnLabel(i));
            }
            result.columns=columns;
            while(rs.next()){
                Map<String,String> row=new HashMap<String, String>();
                for(String column:columns){
                    if(rs.getObject(column)!=null)
                        row.put(column,rs.getObject(column).toString());
                    else
                        row.put(column,"");
                }
                result.datas.add(row);
            }
            result.count=result.datas.size();
            stmt.close();
        }catch(Exception e){
            e.printStackTrace();
            result=null;
        }
        return result;
    }

    public boolean check_table_exist(String table_name){
        boolean result=true;
        Statement stmt = null;
        try {
            stmt = this.sqlite_connn.createStatement();
            ResultSet rs=stmt.executeQuery("select count(1) as ct from sqlite_master "+
                "where type=\"table\" AND name = \""+
                table_name+"\"");
            if(rs!=null){
                if(rs.getInt("ct")==0){
                    result=false;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public int get_query_count(String sqlstr){
        int result=-1;
        Statement stmt = null;
        try {
            stmt = this.sqlite_connn.createStatement();
            ResultSet rs=stmt.executeQuery(sqlstr);
            if(rs!=null){
                result=rs.getInt("ct");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public boolean create_sqlite_db() throws SQLException{
        boolean result=true;
        this.create_sqlite_host_setting();
        this.create_sqlite_protocol();
        this.create_sqlite_channel();
        this.create_sqlite_channel4collect();
        this.create_sqlite_dataItem();
        this.create_sqlite_dataItem4collect();
        this.create_sqlite_dataItem2channel();
        this.create_sqlite_channel_data_filter();
        this.create_sqlite_farm_period_predict_data();
        this.create_sqlite_data_simulator();
        return result;
    }

    private void create_sqlite_host_setting() throws SQLException{
        if(this.check_table_exist("host_setting")==true)return;
        Statement stmt = this.sqlite_connn.createStatement();
        String sqlstr="CREATE TABLE \"host_setting\" (\n"+
            "\"sid\"	INTEGER, \n"+
            "\"title\"	VARCHAR(100),\n"+
            "\"value\"	VARCHAR(100),\n"+
            "\"description\"	VARCHAR(500),\n"+
            "PRIMARY KEY(\"sid\" AUTOINCREMENT)\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        sqlstr="insert into host_setting(title,value)";
        sqlstr+=" values(\"data collect cycle\",60)";
        stmt.executeUpdate(sqlstr);
        stmt.close();
    }

    private void create_sqlite_protocol() throws SQLException{
        if(this.check_table_exist("protocol")==true)return;
        Statement stmt = this.sqlite_connn.createStatement();
        String sqlstr="CREATE TABLE \"protocol\" (\n"+
            "\"protocol_id\"	INTEGER NOT NULL UNIQUE,\n"+
            "\"protocol\"	VARCHAR(50) NOT NULL,\n"+
            "\"type\"	INTEGER DEFAULT 0,\n"+
            "\"memo\"	VARCHAR(200),\n"+
            "PRIMARY KEY(\"protocol_id\" AUTOINCREMENT)\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        sqlstr="insert into protocol(protocol_id,protocol,type)";
        stmt.executeUpdate(sqlstr+" values(0,'DEFAULT',0)");
        stmt.executeUpdate(sqlstr+" values(1,'MODBUS_TCP_SLAVE',0)");
        stmt.executeUpdate(sqlstr+" values(2,'MODBUS_RTU_SLAVE',0)");
        stmt.executeUpdate(sqlstr+" values(3,'P102Mengxi',0)");
        stmt.executeUpdate(sqlstr+" values(4,'P104_Master',1)");
        stmt.executeUpdate(sqlstr+" values(5,'P104_RTU',0)");
        stmt.executeUpdate(sqlstr+" values(6,'MODBUS_TCP_Master',1)");
        stmt.executeUpdate(sqlstr+" values(7,'MODBUS_RTU_Master',1)");
        stmt.close();
    }

    private void create_sqlite_channel() throws SQLException{
        if(this.check_table_exist("channel")==true)return;
        Statement stmt = this.sqlite_connn.createStatement();
        String sqlstr="CREATE TABLE \"channel\" (\n"+
            "\"channel_id\"	INTEGER, \n"+
            "\"name\"	VARCHAR(100),\n"+
            "\"host_ip\"	VARCHAR(100),\n"+
            "\"port\"	INTEGER, \n"+
            "\"protocol\"	INTEGER,\n"+
            "\"data_type\"	INTEGER DEFAULT 0,\n"+
            "\"type\"	INTEGER DEFAULT 0,\n"+
            "\"channel4collect_id\"	INTEGER,\n"+
            "\"path\"	VARCHAR(100),\n"+
            "\"filename\"	VARCHAR(100),\n"+
            "\"com\"	VARCHAR(100), \n"+
            "\"band_rate\"	INTEGER, \n"+
            "\"parity\"	INTEGER DEFAULT 2, \n"+
            "\"stop\"	INTEGER DEFAULT 1, \n"+
            "\"enable\"	INTEGER DEFAULT 1, \n"+
            "\"create_time\"	DATETIME,\n"+
            "PRIMARY KEY(\"channel_id\" AUTOINCREMENT)\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        Date now=new Date();
        SimpleDateFormat ft = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
		sqlstr="insert into channel(channel_id,name,host_ip,port,protocol,data_type,path,filename,com,band_rate,create_time)";
		sqlstr+=String.format(" values(1,'local','127.0.0.1',7788,0,0,'data','local_data','com1',9600,'%s')",ft.format(now));
        stmt.executeUpdate(sqlstr);
        stmt.close();
    }

    private void create_sqlite_channel4collect() throws SQLException{
        if(this.check_table_exist("channel4collect")==true)return;
        Statement stmt = this.sqlite_connn.createStatement();
        String sqlstr="CREATE TABLE \"channel4collect\" (\n"+
            "\"channel_id\"	INTEGER, \n"+
            "\"name\"	VARCHAR(100),\n"+
            "\"dataitem_type\"	INTEGER,\n"+
            "\"host_ip\"	VARCHAR(100),\n"+
            "\"port\"	INTEGER, \n"+
            "\"protocol\"	INTEGER,\n"+
            "\"path\"	VARCHAR(100),\n"+
            "\"filename\"	VARCHAR(100),\n"+
            "\"com\"	VARCHAR(100), \n"+
            "\"band_rate\"	INTEGER, \n"+
            "\"parity\"	INTEGER DEFAULT 2, \n"+
            "\"stop\"	INTEGER DEFAULT 1, \n"+
            "\"enable\"	INTEGER DEFAULT 1, \n"+
            "\"slave_id\"	INTEGER DEFAULT 1, \n"+
            "\"register_type\"	INTEGER DEFAULT 3, \n"+
            "\"address\"	INTEGER DEFAULT 0, \n"+
            "\"count\"	INTEGER DEFAULT 1, \n"+
            "\"run_cycle\"	INTEGER DEFAULT 60, \n"+
            "\"create_time\"	DATETIME,\n"+
            "PRIMARY KEY(\"channel_id\" AUTOINCREMENT)\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        stmt.close();
    }

    private void create_sqlite_dataItem() throws SQLException{
        if(this.check_table_exist("dataItem")==true)return;
        Statement stmt = this.sqlite_connn.createStatement();
        String sqlstr="CREATE TABLE \"dataItem\" (\n"+
            "\"channel_id\"	INTEGER, \n"+
            "\"dataItem_id\"	INTEGER, \n"+
            "\"slave_id\"	INTEGER, \n"+
            "\"register_type\"	INTEGER, \n"+
            "\"data_index\"	INTEGER, \n"+
            "\"address\"	INTEGER, \n"+
            "\"label\"	VARCHAR(100),\n"+
            "\"data_type\"	INTEGER,\n"+
            "\"coefficient\"	FLOAT, \n"+
            "\"negation\"	INTEGER, \n"+
            "\"BCD\"	INTEGER, \n"+
            "\"ABS\"	INTEGER, \n"+
            "\"base_value\"	FLOAT, \n"+
            "\"introduction\"	VARCHAR(500),\n"+
            "\"data_table\"	VARCHAR(100),\n"+
            "\"field\"	VARCHAR(100),\n"+
            "\"value\"	INTEGER,\n"+
            "\"timestamp\"	DATETIME,\n"+
            "\"create_time\"	DATETIME,\n"+
            "PRIMARY KEY(\"dataItem_id\" AUTOINCREMENT)\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        sqlstr="CREATE INDEX \"dataItem_index\" ON \"dataItem\" (\n"+
            "\"dataItem_id\"\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        sqlstr="CREATE INDEX \"dataItem_index2\" ON \"dataItem\" (\n"+
            "\"channel_id\",\n"+
            "\"dataItem_id\"\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        // 创建线圈、离散量、输入寄存器、保存寄存器各100个数据点
        Date now=new Date();
        SimpleDateFormat ft = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
        System.out.println("初始化寄存器数据");
        int reg_length,value;
        for(int i=0;i<4;i++){
            reg_length=this.reg_length;
			// if(i<2){
			// 	reg_length=4096;
            // }else{
			// 	reg_length=10240;
            // }
            for(int j=0;j<reg_length;j++){
                if(i<2)
                    value=j%2;
                else
                    value=j%reg_length;
                sqlstr="insert into dataItem(channel_id,slave_id,register_type,data_index,address,label,data_type,coefficient,negation,BCD,ABS,base_value,introduction,data_table,field,value,create_time) ";
                sqlstr+=String.format(" values(%d,%d,%d,%d,%d,'%s',%d,%f,%d,%d,%d,%f,'%s','%s','%s',%d,'%s')",
                1,1,i+1,j,j,"data-"+(i+1)+"-"+(j),1,1.0,0,0,0,0.0,"","","",value,ft.format(now));
                stmt.executeUpdate(sqlstr);
                System.out.println(String.format("寄存器[%d][%d]",i,j));
            }
        }
        stmt.close();
    }

    private void create_sqlite_dataItem4collect() throws SQLException{
        if(this.check_table_exist("dataItem4collect")==true)return;
        Statement stmt = this.sqlite_connn.createStatement();
        String sqlstr="CREATE TABLE \"dataItem4collect\" (\n"+
            "\"channel_id\"	INTEGER, \n"+
            "\"dataItem_id\"	INTEGER, \n"+
            "\"slave_id\"	INTEGER, \n"+
            "\"register_type\"	INTEGER, \n"+
            "\"data_index\"	INTEGER, \n"+
            "\"address\"	INTEGER, \n"+
            "\"label\"	VARCHAR(100),\n"+
            "\"data_type\"	INTEGER,\n"+
            "\"coefficient\"	FLOAT, \n"+
            "\"negation\"	INTEGER, \n"+
            "\"BCD\"	INTEGER, \n"+
            "\"ABS\"	INTEGER, \n"+
            "\"base_value\"	FLOAT, \n"+
            "\"introduction\"	VARCHAR(500),\n"+
            "\"data_table\"	VARCHAR(100),\n"+
            "\"field\"	VARCHAR(100),\n"+
            "\"value\"	INTEGER,\n"+
            "\"timestamp\"	DATETIME,\n"+
            "\"create_time\"	DATETIME,\n"+
            "PRIMARY KEY(\"dataItem_id\" AUTOINCREMENT)\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        sqlstr="CREATE INDEX \"dataItem4collect_index\" ON \"dataItem4collect\" (\n"+
            "\"channel_id\",\n"+
            "\"dataItem_id\"\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        stmt.close();
    }

    private void create_sqlite_dataItem2channel() throws SQLException{
        if(this.check_table_exist("dataItem2channel")==true)return;
        Statement stmt = this.sqlite_connn.createStatement();
        String sqlstr="CREATE TABLE \"dataItem2channel\" (\n"+
            "\"dataItem2channel_id\"	INTEGER UNIQUE,\n"+
            "\"channel_id\"	INTEGER,\n"+
            "\"dataItem_id\"	INTEGER,\n"+
            "\"label\"	VARCHAR(100),\n"+
            "\"create_time\"	DATETIME,\n"+
            "PRIMARY KEY(\"dataItem2channel_id\" AUTOINCREMENT)\n"+
            ");";
        stmt.executeUpdate(sqlstr);
        stmt.close();
    }

    private void create_sqlite_channel_data_filter() throws SQLException{
        if(this.check_table_exist("channel_data_filter")==true)return;
        Statement stmt = this.sqlite_connn.createStatement();
        String sqlstr="CREATE TABLE \"channel_data_filter\" (\n"+
            "\"channel_data_filter_id\"	INTEGER NOT NULL,\n"+
            "\"channel_id\"	INTEGER,\n"+
            "\"register_type\"	INTEGER,\n"+
            "\"filter\"	varchar(1000),\n"+
            "PRIMARY KEY(\"channel_data_filter_id\" AUTOINCREMENT)\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        stmt.close();
    }

    private void create_sqlite_data_simulator() throws SQLException{
        if(this.check_table_exist("data_simulator")==true)return;
        Statement stmt = this.sqlite_connn.createStatement();
        String sqlstr="CREATE TABLE \"data_simulator\" (\n"+
            "\"data_simulator_id\"	INTEGER NOT NULL,\n"+
            "\"address\"	INTEGER,\n"+
            "\"data_count\"	INTEGER,\n"+
            "\"data_start\"	INTEGER,\n"+
            "\"data_end\"	INTEGER,\n"+
            "\"data_type\"	INTEGER,\n"+
            "\"method\"	INTEGER,\n"+
            "\"run_cycle\"	INTEGER,\n"+
            "PRIMARY KEY(\"data_simulator_id\" AUTOINCREMENT)\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        stmt.close();
    }

    private void create_sqlite_farm_period_predict_data() throws SQLException{
        if(this.check_table_exist("farm_period_predict_data")==true)return;
        Statement stmt = this.sqlite_connn.createStatement();
        String sqlstr="CREATE TABLE \"farm_period_predict_data\" (\n"+
            "\"farm_period_predict_data_id\"	INTEGER NOT NULL,\n"+
            "\"farm_period_id\"	INTEGER,\n"+
            "\"model_id\"	INTEGER,\n"+
            "\"data_time\"	DATETIME,\n"+
            "\"forecast_time\"	DATETIME,\n"+
            "\"predict_power\"	FLOAT,\n"+
            "\"speed\"	FLOAT,\n"+
            "\"direction\"	FLOAT,\n"+
            "\"radiation\"	FLOAT,\n"+
            "PRIMARY KEY(\"farm_period_predict_data_id\" AUTOINCREMENT)\n"+
            ")";
        stmt.executeUpdate(sqlstr);
        stmt.close();
    }

    public void read_conf() throws Exception{
        Map<String,String> map = DRCDB.readIniFile(this.ConfFile);
        this.DBFile = map.get("DBFile");
        this.WEBPort=Integer.parseInt(map.get("WEBPort"));
    }

    /**
     * @author luojidong
     * @return map<ini data>
     * @throws Exception
     */
    public static Map<String,String> readIniFile(String filename) throws Exception {
//        InputStream in = DRCDB.class.getClassLoader().getResourceAsStream(filename);
        
        InputStream in = new FileInputStream(new File(filename));
        BufferedReader br = new BufferedReader(new InputStreamReader(in));  
        Properties props = new Properties();  
        props.load(br);  
        Map<String, String> map = new HashMap<String, String>();
        for(Object s: props.keySet()){  
            //System.out.println(s+":"+props.getProperty(s.toString()));
            map.put(s.toString(), props.getProperty(s.toString()));
        }
        return map;
    }

    /**
     * 读取配置文件指定键
     * @throws Exception
     */
    public static String read_conf_with_name(String key,String ConfFile){
        if(ConfFile==null||ConfFile.equals(""))ConfFile=System.getProperty("user.dir")+"/conf.ini";
        // System.out.println(key);
        Map<String, String> map;
        String result="";
        try {
            map = readIniFile(ConfFile);
            result= map.get(key);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static void main(String[] args){
        DRCDB drcdb=new DRCDB();
        record_data_set rs;
        rs=drcdb.executeQuerySQL("select * from protocol");
        for(Map<String,String> row:rs.datas){
            System.out.println(row.get("protocol"));
        }
    }
}